package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.OrderDetail;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.Organization;
import com.xmy.cultivate.entity.Subjects;
import com.xmy.cultivate.entity.views.*;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-01
 */
public interface OrderDetailMapper extends BaseMapper<OrderDetail> {


    /**
     * 报读列表
     * @return
     */
    @Select("select * from order_detail ${ew.customSqlSegment}")
    public abstract IPage<ResApplyGather> getOrderDetailListForPage(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from order_detail ${ew.customSqlSegment}")
    public abstract List<ResApplyGather> getOrderDetailList(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 报读列表
     * @return
     */
    @Select("select * from enroll_view ${ew.customSqlSegment}")
    public abstract IPage<EnrollList> getEnrollList(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select sum(solid_price) as solid_price from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getSolidPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(DISTINCT student_id) as count  from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getAllStudentCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail ${ew.customSqlSegment}")
    public abstract List<OrderDetail> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail where school_id = #{schoolId} and student_id = #{studentId} and course_id = #{courseId} and deleted = 0 and status<>2 and residue_course_count>0")
    public abstract List<OrderDetail> orderDetailListForCourse(Long schoolId,Long studentId,Long courseId);


    //由于要纠正数据，等于0的限制暂时取消，后面恢复，and residue_course_count>0
    @Select("select id,order_id,course_price_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,residue_price,student_id from order_detail where school_id = #{schoolId} and student_id = #{studentId} and subjects_id = #{subjectsId} and deleted = 0 and status<>2 and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType} and year_class_id = ${yearClassId} order by year_and_quarter,id asc")
    public abstract List<OrderDetail> orderDetailListForSubjects(@Param("schoolId")Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType,@Param("yearClassId") Integer yearClassId);



    //public abstract List<Map<String,Object>> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取该科目是否报读过
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select count(a.id) as count from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3")
    public  abstract  Integer getApplyCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum);

    @Select("select a.id,a.year_part as yearPart,a.is_new_student as isNewStudent,c.quarter_num as quarterNum from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3 ORDER BY a.created_at desc limit 0,1 ")
    public  abstract  Map<String,Object> getApplyCourseForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum);


    /**
     * 获取某科目最后一次报读的数据
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select a.* from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.student_id = #{studentId} and a.subjects_id=#{subjectsId} and b.status <> 2 and a.deleted = 0 order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForSubjectId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status<>2 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType} and year_class_id = ${yearClassId}")
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType,@Param("yearClassId") Integer yearClassId);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status<>2 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType}")
    public abstract Integer getResidueCourseCountForSubjectsNoYearClassId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    /**
     * 获取报读课程的最后一条数据
     * @param schoolId
     * @param studentId
     * @param courseId
     * @return
     */
    @Select("select a.* from order_detail as a where a.school_id = #{schoolId}  a.student_id = #{studentId} and a.course_id=#{courseId} and b.status <> 2 and a.deleted = 0 and a.residue_course_count>0 order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForCourseId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("courseId") Long courseId);


    /**
     * 获取科目的剩余课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.residue_course_count/3),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract Integer getResidueCourseCount(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    /**
     * 获取科目的剩余课次
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.residue_course_count/3),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract Integer getResidueCourseCountNotSchoolId(@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    /**
     * 获取所报读班级的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(residue_price) as residue_price from order_detail as a where id in (select order_detail_id from student_grade as b where b.student_id=#{studentId} and b.grade_id=#{gradeId} and b.status =1 and b.reading_status=1 and b.school_id=#{schoolId}) and a.reading_status = 1 and a.deleted = 0")
    public abstract OrderDetailTiny getResidueCourseCountForGrade(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("schoolId") Long schoolId);

    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract OrderDetailTiny getResidueCourseCountForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId);

    /**
     * 获取所报读科目的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and subjects_id=#{subjectsId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract OrderDetailTiny getResidueCourseCountForSubjectsId(@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("schoolId") Long schoolId);



    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract Integer getResidueCourseHouseForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId);

    /**
     * 办理中心获取学员报读信息
     * @return
     */
    //@Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2  and a.residue_course_count > 0 and a.student_id = #{studentId} and a.deleted=0  group by course_id order by a.id desc ")
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id ${ew.customSqlSegment} ")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            ),
            @Result(
                    property = "organizationModel",
                    column = "school_id",
                    javaType = Organization.class,
                    one = @One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract List<OrderDetail> GetLessonByStuInfoId(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取报读科目
     * @param wrapper
     * @return
     */
    @Select("select a.subjects_id as id,a.course_id,b.`name`,a.school_id from order_detail as a LEFT JOIN subjects as b on a.subjects_id = b.id ${ew.customSqlSegment} ")
    public abstract List<Subjects> GetSubjectsByStuInfoId(@Param(Constants.WRAPPER) Wrapper wrapper);



    /**
     * 报读通过课程，获取在读信息
     * @param courseId
     * @param studentId
     * @return
     */
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2 and a.deleted=0 and a.residue_course_count > 0 and a.student_id = #{studentId} and a.course_id = #{courseId} and a.school_id=#{schoolId}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderCoursePricingModel",
                    column = "id",
                    one = @One(select = "com.xmy.cultivate.mapper.OrderCoursePricingMapper.selectById")
            )
    })
    public abstract List<OrderDetail> getLessonByCourseId(@Param("courseId") Long courseId,@Param("studentId") Long studentId,@Param("schoolId") Long schoolId);

    /**
     * 报读通过科目，获取在读信息
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2 and a.deleted=0 and a.residue_course_count > 0 and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.school_id=#{schoolId}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderCoursePricingModel",
                    column = "id",
                    one = @One(select = "com.xmy.cultivate.mapper.OrderCoursePricingMapper.selectById")
            )
    })
    public abstract List<OrderDetail> getLessonForSubjects(@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId,@Param("schoolId") Long schoolId);


    /**
     * 获取报读校区
     * @param studentId
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id where a.student_id = #{studentId} and a.status <> 2 GROUP BY a.school_id")
    public abstract List<Organization> getSignUpSchoolList(@Param("studentId") Long studentId);

    /**
     * 获取报读校区
     * @param
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id ${ew.customSqlSegment}")
    public abstract List<Organization> getSignUpSchoolListForWhere(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 获取对应课程最早的报读信息，记上课，扣课时用
     * @param gradeId 班级id
     * @param count 此次扣多少个课时
     * @return
     */
    @Select("select a.id,a.order_detail_id,a.student_id,a.grade_id,a.use_course_count,a.year_part,\n" +
            "b.residue_course_count,b.residue_price,b.all_course_count,b.serve_price,b.materials_price\n" +
            "from student_grade as a LEFT JOIN order_detail as b on a.order_detail_id = b.id\n" +
            "where a.deleted = 0 and a.`status` = 1 and a.reading_status = 1 and b.reading_status = 1 and b.`status`<>2 and b.residue_course_count >= #{count} and a.student_id = #{studentId} and a.grade_id=#{gradeId} \n" +
            "ORDER BY b.id DESC limit 0,1")
    public abstract FirstSignUp getFirstSignUp(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("count") Integer count);

    @Select("select ifnull(sum(use_course_count),0) as use_course_count from order_detail where order_id = #{orderId}")
    public abstract Integer getUseHouseForOrderId(@Param("orderId") Long orderId);

    @Select("select * from income_day_report_view ${ew.customSqlSegment}")
    public abstract IPage<IncomeDayReport> getIncomeDayReport(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取所有课时
     * @param schoolId
     * @param studentId
     * @param subjects_id
     * @return
     */
    @Select("select (sum(all_course_count) - sum(out_house)) as all_count from (select all_course_count,(select IFNULL(sum(out_house),0) from order_transfer_detail as b where b.old_order_detail_id = a.id and b.status<>2) as out_house from order_detail as a where a.deleted = 0 and a.`status`<>2 and a.student_id = #{studentId} and a.school_id=#{schoolId} and subjects_id=#{subjectsId}) as t")
    public abstract Integer getAllCouseHouse(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjects_id);

    /**
     * 获取所有实收金额
     * @return
     */
    @Select("select sum(solid_price) as allSolidPrice from order_detail as a where a.deleted = 0 and a.`status`<>2 and order_type=1 and a.student_id = #{studentId} and a.school_id=#{schoolId} and subjects_id=#{subjectsId}")
    public abstract BigDecimal getAllSolidPrice(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjects_id);


    /**
     * 财务报表
     * @param page
     * @param wrapper
     * @return
     */
    @Select("select a.student_id,a.grade_id,a.subjects_id,SUM(a.all_course_count) as all_course_count,SUM(a.use_course_count) as use_course_count,SUM(a.residue_course_count) as residue_course_count,SUM(a.price) as price,SUM(a.discounts_price) as discounts_price,SUM(residue_price) as residue_price,SUM(a.out_price) as out_price,SUM(a.solid_price) as solid_price,SUM(a.use_price) as reality_pirce from order_detail as a ${ew.customSqlSegment}")
    public abstract IPage<StatementData> statementData(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取退费金额
     * @param wrapper
     * @return
     */
    @Select("select IFNULL(SUM(a.out_price),0) as out_price from order_transfer_detail as a LEFT JOIN order_detail as b on a.old_order_detail_id = b.id ${ew.customSqlSegment}")
    public abstract BigDecimal getStudentOutPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取学生使用金额
     * @param wrapper
     * @return
     */
    @Select("select a.student_id,a.school_id,a.subjects_id,SUM(b.buckle_class_house) as buckle_class_house,SUM(b.buckle_class_money) as buckle_class_money,SUM(b.serve_price) as serve_price from record_course as a LEFT JOIN record_cost as b on a.id = b.id ${ew.customSqlSegment}")
    public abstract BigDecimal getStudentUsePrice(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select("select count(*) from (select * from (select sum(residue_course_count) as residue_course_count,school_id,student_id from order_detail ${ew.customSqlSegment}) as t where 1=1 ${where02}) as tc")
    public abstract Integer getCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("where02") String where02);

    @Select("select sum(discounts_price) as discounts_price from order_detail where order_id = ${orderId}")
    public abstract BigDecimal getOrderDiscounts(@Param("orderId") Long orderId);


    /**
     * 临时获取寒假大于15课次的数据，以后可以删除掉
     * @return
     */
    @Select("select * from (select SUM(all_course_count-roll_out_house) as all_course_count,student_id,subjects_id,year_part,quarter_num,created_at,id from order_detail where year_part = 2023 and quarter_num = 1 and deleted = 0 and status<>2 and out_house = 0 and order_type in(1,3) GROUP BY student_id,subjects_id,year_part,quarter_num ORDER BY created_at) as t where all_course_count>=45")
    public abstract List<OrderDetail> getBigFifteeList();


    @Select("select * from (select IFNULL(SUM(use_course_count),0) as use_course_count from (select a.use_course_count,b.teacher_id,a.year_part,a.quarter_num,b.school_id,a.subjects_id,student_id from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id where student_id) as t ${ew.customSqlSegment}) as a ")
    public abstract Integer getUseCourseHouse(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select SUM(a.residue_course_count) as residue_course_count from order_detail as a LEFT JOIN student as b on a.student_id = b.id where a.year_and_quarter < 20232 and a.residue_course_count>0 and a.student_id = #{studentId}")
    public abstract Integer getCourseHouse(Long studentId);

    /**
     * 获取剩余课时大于0的订单，初始化班型用
     * @return
     */
    @Select("select school_id,student_id,subjects_id,year_class_id,residue_course_count_all from (select *,sum(residue_course_count) as residue_course_count_all from order_detail where deleted=0 and year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`<>2  GROUP BY school_id,student_id,subjects_id) as t where residue_course_count_all>0 ORDER BY subjects_id asc")
    public abstract List<OrderDetail> getOrderDetailForResidueCoruseCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    @Select("select * from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type =${lessonType} and student_id = ${studentId} and subjects_id =${subjectsId} and deleted = 0 ORDER BY id desc limit 1")
    public abstract OrderDetail getLastOneForStudent(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);

    /**
     * 获取科目的总课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select({"<script>",
            "select IFNULL(sum((a.all_course_count-a.out_house-a.turn_out_house)/3),0) as allCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where 1=1 ",
            "<when test='schoolId!=null'>",
            "and a.school_id= #{schoolId} ",
            "</when>",
            "and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status  &lt;&gt; 2 and a.year_and_quarter &gt;= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ",
            "</script>"})
    public abstract Integer getAllCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


}
